package com.qiangesoft.easyexcel.read;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.qiangesoft.easyexcel.common.ExcelStringConverter;
import com.qiangesoft.easyexcel.util.ResourceFileUtil;
import lombok.extern.slf4j.Slf4j;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * excel读取工具类
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
@Slf4j
public class ExcelRead {

    /**
     * 读数据-自定义监听器
     *
     * @param fileName
     * @throws IOException
     */
    public static void read(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener())
                .sheet()
                .doRead();
    }

    /**
     * 读数据-自定义监听器，指定sheetNo
     *
     * @param fileName
     * @param sheetNo
     * @throws IOException
     */
    public static void read(String fileName, int sheetNo) throws IOException {
        read(fileName, sheetNo, 1);
    }

    /**
     * 读数据-自定义监听器，指定sheetNo,headRowNumber
     *
     * @param fileName
     * @param sheetNo
     * @throws IOException
     */
    public static void read(String fileName, int sheetNo, int headRowNumber) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        try (com.alibaba.excel.ExcelReader excelReader = EasyExcel.read(inputStream, ReadData.class, new ReadDataListener()).build()) {
            // 构建一个sheet
            ReadSheet readSheet = EasyExcel.readSheet(sheetNo).headRowNumber(headRowNumber).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        }
    }

    /**
     * 读数据-多个相同的sheet
     *
     * @param fileName
     * @throws IOException
     */
    public static void readAllSheet(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        // 读取全部sheet，所有sheet页数据格式相同
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener()).doReadAll();
    }

    /**
     * 读数据-多个不同的sheet
     *
     * @param fileName
     * @throws IOException
     */
    public static void readManySheet(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        try (com.alibaba.excel.ExcelReader excelReader = EasyExcel.read(inputStream).build()) {
            // 方便测试
            ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ReadData.class).registerReadListener(new ReadDataListener()).build();
            ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ReadData.class).registerReadListener(new ReadDataListener()).build();
            excelReader.read(readSheet1, readSheet2);
        }
    }

    /**
     * 读数据-额外配置
     *
     * @param fileName
     * @throws IOException
     */
    public static void readExtra(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener())
                // 注册转换器
                .registerConverter(new ExcelStringConverter())
                // 需要读取批注 默认不读取
                .extraRead(CellExtraTypeEnum.COMMENT)
                // 需要读取超链接 默认不读取
                .extraRead(CellExtraTypeEnum.HYPERLINK)
                // 需要读取合并单元格信息 默认不读取
                .extraRead(CellExtraTypeEnum.MERGE)
                .sheet()
                .doRead();
    }

    /**
     * 读数据-同步返回
     *
     * @param fileName
     * @throws IOException
     */
    public static List<ReadData> readSync(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        return EasyExcel.read(inputStream)
                .head(ReadData.class)
                .sheet()
                .doReadSync();
    }

    /**
     * 读数据-内置监听器
     *
     * @param fileName
     * @throws IOException
     */
    public static void readBuiltIn(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);

        // 默认每次会读取100条数据
        EasyExcel.read(inputStream, ReadData.class, new PageReadListener<ReadData>(dataList -> {
            // todo 存储数据
            log.info("{}条数据存储数据库成功！", dataList.size());
        }, 100)).sheet().doRead();
    }

    /**
     * 读数据-匿名函数
     *
     * @param fileName
     * @throws IOException
     */
    public static void readAnonymous(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);

        EasyExcel.read(inputStream, ReadData.class, new ReadListener<ReadData>() {
            /**
             * 每隔100条存储数据库，然后清理list
             */
            private static final int BATCH_COUNT = 100;

            /**
             * 缓存的数据
             */
            private List<ReadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            /**
             * 每一条数据解析都会调用
             *
             * @param data
             * @param context
             */
            @Override
            public void invoke(ReadData data, AnalysisContext context) {
                cachedDataList.add(data);
                // 达到BATCH_COUNT了，需要去存储一次数据库，防止几万条数据在内存，造成OOM
                if (cachedDataList.size() >= BATCH_COUNT) {

                    this.saveData();

                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            /**
             * 所有数据解析完成调用
             *
             * @param context
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 确保最后遗留的数据也存储到数据库
                this.saveData();

                log.info("所有数据解析完成！");
            }

            /**
             * 存储数据
             */
            private void saveData() {
                log.info("{}条数据存储数据库成功！", cachedDataList.size());
            }
        }).sheet().doRead();
    }

}
